use cdb::{Database, Result};
use tempfile::TempDir;

/// 创建测试数据库
async fn create_test_database() -> Result<(Database, TempDir)> {
    let temp_dir = tempfile::tempdir().unwrap();
    let db = Database::new().await?;
    Ok((db, temp_dir))
}

#[tokio::test]
async fn test_integer_data_type() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建包含INTEGER类型的表（使用唯一表名）
    let table_name = format!("test_integers_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        small_int INTEGER,
        big_int INTEGER,
        negative_int INTEGER
    )", table_name)).await.unwrap();

    // 插入不同范围的整数（避免使用i64::MIN，因为解析器可能有问题）
    conn.execute(&format!("INSERT INTO {} VALUES (1, 42, 9223372036854775807, -123)", table_name)).await.unwrap();
    conn.execute(&format!("INSERT INTO {} VALUES (2, 0, 1000000, -1000000)", table_name)).await.unwrap();

    // 查询验证
    let result = conn.execute(&format!("SELECT * FROM {} ORDER BY id", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 2);
    
    println!("INTEGER测试通过 ✅");
}

#[tokio::test]
async fn test_text_data_type() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建包含TEXT类型的表（使用唯一表名）
    let table_name = format!("test_texts_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        short_text TEXT,
        long_text TEXT,
        empty_text TEXT,
        unicode_text TEXT
    )", table_name)).await.unwrap();

    // 插入不同类型的文本
    conn.execute(&format!("INSERT INTO {} VALUES (
        1,
        'Hello',
        'This is a very long text string that should be handled properly by the database system',
        '',
        '你好世界🌍'
    )", table_name)).await.unwrap();

    // 查询验证
    let result = conn.execute(&format!("SELECT * FROM {}", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 1);
    
    println!("TEXT测试通过 ✅");
}

#[tokio::test]
async fn test_real_data_type() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建包含REAL类型的表（使用唯一表名）
    let table_name = format!("test_reals_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        pi REAL,
        negative_real REAL,
        zero_real REAL,
        large_real REAL
    )", table_name)).await.unwrap();

    // 插入不同的浮点数
    conn.execute(&format!("INSERT INTO {} VALUES (1, 3.14159, -2.71828, 0.0, 1.23456789e10)", table_name)).await.unwrap();

    // 查询验证
    let result = conn.execute(&format!("SELECT * FROM {}", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 1);
    
    println!("REAL测试通过 ✅");
}

#[tokio::test]
async fn test_boolean_data_type() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建包含BOOLEAN类型的表（使用唯一表名）
    let table_name = format!("test_booleans_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        is_active BOOLEAN,
        is_deleted BOOLEAN
    )", table_name)).await.unwrap();

    // 插入布尔值
    conn.execute(&format!("INSERT INTO {} VALUES (1, true, false)", table_name)).await.unwrap();
    conn.execute(&format!("INSERT INTO {} VALUES (2, false, true)", table_name)).await.unwrap();

    // 查询验证
    let result = conn.execute(&format!("SELECT * FROM {} ORDER BY id", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 2);
    
    println!("BOOLEAN测试通过 ✅");
}

#[tokio::test]
async fn test_mixed_data_types() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建包含多种数据类型的表（使用唯一表名）
    let table_name = format!("test_mixed_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER,
        salary REAL,
        is_manager BOOLEAN
    )", table_name)).await.unwrap();

    // 插入混合数据类型
    conn.execute(&format!("INSERT INTO {} VALUES (1, 'Alice', 30, 75000.50, true)", table_name)).await.unwrap();
    conn.execute(&format!("INSERT INTO {} VALUES (2, 'Bob', 25, 60000.0, false)", table_name)).await.unwrap();
    conn.execute(&format!("INSERT INTO {} VALUES (3, 'Charlie', 35, 85000.75, true)", table_name)).await.unwrap();

    // 查询验证
    let result = conn.execute(&format!("SELECT * FROM {} ORDER BY id", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 3);

    // 测试WHERE条件中的类型比较
    let result = conn.execute(&format!("SELECT * FROM {} WHERE age > 28", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 2);

    let result = conn.execute(&format!("SELECT * FROM {} WHERE salary > 70000.0", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 2);

    let result = conn.execute(&format!("SELECT * FROM {} WHERE is_manager = true", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 2);

    println!("混合数据类型测试通过 ✅");
}

#[tokio::test]
async fn test_data_type_conversions() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建测试表（使用唯一表名）
    let table_name = format!("test_conversions_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        int_val INTEGER,
        real_val REAL,
        text_val TEXT
    )", table_name)).await.unwrap();

    // 插入数据
    conn.execute(&format!("INSERT INTO {} VALUES (1, 42, 3.14, '123')", table_name)).await.unwrap();

    // 查询验证
    let result = conn.execute(&format!("SELECT * FROM {}", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 1);

    println!("数据类型转换测试通过 ✅");
}

#[tokio::test]
async fn test_numeric_edge_cases() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建数值边界测试表
    let table_name = format!("test_numeric_edges_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        max_int INTEGER,
        min_int INTEGER,
        zero_int INTEGER,
        positive_real REAL,
        negative_real REAL,
        zero_real REAL,
        small_real REAL,
        large_real REAL
    )", table_name)).await.unwrap();

    // 插入边界值（使用合理的大数值）
    conn.execute(&format!("INSERT INTO {} VALUES (
        1,
        2147483647,
        -2147483648,
        0,
        3.4028235e38,
        -3.4028235e38,
        0.0,
        1.175494e-38,
        1.23456789e30
    )", table_name)).await.unwrap();

    // 查询验证
    let result = conn.execute(&format!("SELECT * FROM {}", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 1);

    println!("数值边界测试通过 ✅");
}

#[tokio::test]
async fn test_text_edge_cases() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建文本边界测试表
    let table_name = format!("test_text_edges_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        empty_text TEXT,
        single_char TEXT,
        unicode_text TEXT,
        special_chars TEXT,
        long_text TEXT
    )", table_name)).await.unwrap();

    // 插入各种文本
    conn.execute(&format!("INSERT INTO {} VALUES (
        1,
        '',
        'A',
        '你好世界🌍🚀💻',
        '!@#$%^&*()_+-=[]{{}}|;:,.<>?',
        '{}'
    )", table_name, "A".repeat(1000))).await.unwrap();

    // 查询验证
    let result = conn.execute(&format!("SELECT * FROM {}", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 1);

    println!("文本边界测试通过 ✅");
}

#[tokio::test]
async fn test_boolean_variations() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建布尔值测试表
    let table_name = format!("test_bool_variations_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        bool_true BOOLEAN,
        bool_false BOOLEAN
    )", table_name)).await.unwrap();

    // 插入不同的布尔值表示
    conn.execute(&format!("INSERT INTO {} VALUES (1, true, false)", table_name)).await.unwrap();
    conn.execute(&format!("INSERT INTO {} VALUES (2, false, true)", table_name)).await.unwrap();

    // 查询验证
    let result = conn.execute(&format!("SELECT * FROM {} ORDER BY id", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 2);

    println!("布尔值变体测试通过 ✅");
}

#[tokio::test]
async fn test_null_values() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建允许NULL值的表（使用唯一表名）
    let table_name = format!("test_nulls_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        optional_text TEXT,
        optional_int INTEGER,
        optional_real REAL,
        optional_bool BOOLEAN
    )", table_name)).await.unwrap();

    // 插入包含NULL值的数据（使用完整VALUES语法）
    conn.execute(&format!("INSERT INTO {} VALUES (1, 'not null', NULL, NULL, NULL)", table_name)).await.unwrap();
    conn.execute(&format!("INSERT INTO {} VALUES (2, NULL, 42, NULL, NULL)", table_name)).await.unwrap();

    // 查询验证
    let result = conn.execute(&format!("SELECT * FROM {} ORDER BY id", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 2);

    println!("NULL值测试通过 ✅");
}

#[tokio::test]
async fn test_type_comparison_operations() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建类型比较测试表
    let table_name = format!("test_type_comparisons_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        int_val INTEGER,
        real_val REAL,
        text_val TEXT,
        bool_val BOOLEAN
    )", table_name)).await.unwrap();

    // 插入测试数据
    conn.execute(&format!("INSERT INTO {} VALUES (1, 10, 10.5, 'apple', true)", table_name)).await.unwrap();
    conn.execute(&format!("INSERT INTO {} VALUES (2, 20, 15.3, 'banana', false)", table_name)).await.unwrap();
    conn.execute(&format!("INSERT INTO {} VALUES (3, 5, 25.7, 'cherry', true)", table_name)).await.unwrap();

    // 测试整数比较
    let result = conn.execute(&format!("SELECT * FROM {} WHERE int_val > 10", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 1);

    // 测试浮点数比较
    let result = conn.execute(&format!("SELECT * FROM {} WHERE real_val < 20.0", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 2);

    // 测试文本比较
    let result = conn.execute(&format!("SELECT * FROM {} WHERE text_val = 'banana'", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 1);

    // 测试布尔值比较
    let result = conn.execute(&format!("SELECT * FROM {} WHERE bool_val = true", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 2);

    println!("类型比较操作测试通过 ✅");
}

#[tokio::test]
async fn test_data_type_storage_efficiency() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建存储效率测试表
    let table_name = format!("test_storage_efficiency_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        small_int INTEGER,
        large_int INTEGER,
        small_real REAL,
        large_real REAL,
        short_text TEXT,
        long_text TEXT
    )", table_name)).await.unwrap();

    // 插入不同大小的数据
    for i in 1..=10 {
        conn.execute(&format!("INSERT INTO {} VALUES (
            {},
            {},
            {},
            {},
            {},
            'text{}',
            '{}'
        )",
            table_name,
            i,
            i,
            i * 1000000000i64,
            i as f64 * 0.123456789,
            i as f64 * 123456.789,
            i,
            "long_text_content_".repeat(i as usize)
        )).await.unwrap();
    }

    // 验证所有数据都正确存储
    let result = conn.execute(&format!("SELECT * FROM {} ORDER BY id", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 10);

    println!("数据类型存储效率测试通过 ✅");
}

#[tokio::test]
async fn test_null_handling_comprehensive() {
    let (db, _temp_dir) = create_test_database().await.unwrap();
    let mut conn = db.connect().await.unwrap();

    // 创建NULL处理测试表
    let table_name = format!("test_null_comprehensive_{}", std::process::id());
    conn.execute(&format!("CREATE TABLE {} (
        id INTEGER PRIMARY KEY,
        nullable_int INTEGER,
        nullable_real REAL,
        nullable_text TEXT,
        nullable_bool BOOLEAN
    )", table_name)).await.unwrap();

    // 插入包含NULL的各种组合
    conn.execute(&format!("INSERT INTO {} VALUES (1, NULL, 1.5, 'text', true)", table_name)).await.unwrap();
    conn.execute(&format!("INSERT INTO {} VALUES (2, 42, NULL, 'text', false)", table_name)).await.unwrap();
    conn.execute(&format!("INSERT INTO {} VALUES (3, 42, 1.5, NULL, true)", table_name)).await.unwrap();
    conn.execute(&format!("INSERT INTO {} VALUES (4, 42, 1.5, 'text', NULL)", table_name)).await.unwrap();
    conn.execute(&format!("INSERT INTO {} VALUES (5, NULL, NULL, NULL, NULL)", table_name)).await.unwrap();

    // 验证所有行都正确插入
    let result = conn.execute(&format!("SELECT * FROM {} ORDER BY id", table_name)).await.unwrap();
    assert_eq!(result.rows().len(), 5);

    println!("NULL处理综合测试通过 ✅");
}
